with customer_project as (--客户,项目
    select t1.project_name                                      --项目名称
         , t1.id                                                --客户id
         , t1.code                                              --客户编码
         , t1.name                                              --客户名称
         , t2.code                     as customer_network_code --客户所属网点code
         , t2.name                     as customer_network_name --客户所属网点name
         , t2.in_distribution_code_new as customer_center_code  --客户所属中心code
         , t2.in_distribution_name_new as customer_center_name  --客户所属中心name
         , t2.agent_code               as customer_agent_code   --客户所属代理区name
         , t2.agent_name               as customer_agent_name   --客户所属代理区name
    from (
             select project_name --项目名称
                  , id           --客户id
                  , code         --客户编码
                  , name         --客户名称
                  , network_id   --客户所属网点
                  , row_number() over (partition by project_name,code order by update_time desc ) as rn
             from jms_dim.dim_lmdm_sys_customer
             where project_name like "%菜鸟4PL项目%"
                or project_name like "%菜鸟M2C项目%"
         ) t1
             left join jms_dim.dim_network_whole_massage t2
                       on t1.network_id = t2.id
),
     platform_effect as (--时效配置
         select start_province    --始发省份
              , start_city        --始发城市
              , end_province      --目的省份
              , end_city          --目的城市
              , platform_effect   --平台时效
              , effect_start_time --有效开始时间
              , effect_end_time   --有效结束时间
         from jms_dim.dim_tab_platform_effect_config_base
         where platform_name = '七星潭'
           and tctatus = 1
     ),
     order_incre as (--订单
         select t1.id                                as order_id                --订单编号
              , waybill_id                                                      --运单号
              , customer_code                                                   --客户编号code
              , customer_name                                                   --客户编号名称
              , pick_time                                                       --取件时间(揽收时间)
              , input_time                                                      --订单录入时间
              , from_unixtime(unix_timestamp(input_time) + 21600,
                              'yyyy-MM-dd HH:mm:ss') as add6h_input_time
              , substr(from_unixtime(unix_timestamp(input_time) + 21600, 'yyyy-MM-dd HH:mm:ss'), 1,
                       10)                           as add6h_date_time
              , case
                    when pick_time <=
                         concat(substr(from_unixtime(unix_timestamp(input_time) + 21600, 'yyyy-MM-dd HH:mm:ss'), 1, 10),
                                ' 23:59:59') then 1
                    else 0 end                       as add6h_timely_pick_falg  --1代表 及时揽收标识
              , real_pick_network_code                                          --实际取件网点code(揽收网点)
              , real_pick_network_name                                          --实际取件网点名称(揽收网点)
              , dispatch_network_code                                           --派件网点code
              , dispatch_network_name                                           --派件网点名称
              , if(order_status_code = 104, 1, 0)    as cancel_flag             --订单取消1代表取消,0代表未取消
              , order_status_code                                               --订单状态code
              , t2.provider_id                       as real_pick_provider_id   --(揽收网点)实际取件网点省id
              , t2.provider_desc                     as real_pick_provider_desc --(揽收网点)实际取件网点省desc
              , t2.city_id                           as real_pick_city_id       --(揽收网点)实际取件网点市id
              , t2.city_desc                         as real_pick_city_desc     --(揽收网点)实际取件网点市desc
              , t3.provider_id                       as dispatch_provider_id    --派件网点省id
              , t3.provider_desc                     as dispatch_provider_desc  --派件网点省desc
              , t3.city_id                           as dispatch_city_id        --派件网点市id
              , t3.city_desc                         as dispatch_city_desc      --派件网点市desc
         from jms_dwd.dwd_yl_oms_oms_order_incre_dt t1
                  left join jms_dim.dim_network_whole_massage t2
                            on t1.real_pick_network_code = t2.code
                  left join jms_dim.dim_network_whole_massage t3
                            on t1.dispatch_network_code = t3.code
         where dt >= date_add('{{ execution_date |  cst_ds }}', -45)
           and dt <= '{{ execution_date |  cst_ds }}'
           and customer_code is not null
           and input_time is not null
     ),
     waybill_incre as (--运单
         select waybill_no                               --运单号
              , sign_time                                --签收时间
              , sign_network_code                        --签收网点code
              , sign_network_name                        --签收网点名称
              , input_time             as way_input_time --运单录入时间
              , waybill_status_code                      --运单状态code
              , if(is_sign <> 0, 1, 0) as sign_falg      --签收标识1正常签收和退件签收 0未签收
         from jms_dwd.dwd_yl_oms_oms_waybill_incre_dt
         where dt >= date_add('{{ execution_date |  cst_ds }}', -45)
           and dt <= '{{ execution_date |  cst_ds }}'
           and waybill_no is not null
     ),
     collect_base as (
         select waybill_no
              , min(input_time) as input_time
         from jms_dwd.dwd_tab_barscan_collect_base_dt
         where dt >= date_add('{{ execution_date |  cst_ds }}', -45)
           and dt <= '{{ execution_date |  cst_ds }}'
           and scan_type = '入库扫描'
         group by waybill_no
     ),
     express_wo as (--破损量 ----------------------------------------------------------------------------------------
         select waybill_no
              , problem_type_code         --问题件类型编码
              , problem_type_name         --问题件类型名称
              , problem_type_subject_code --问题件二级类型编码
              , problem_type_subject_name --问题件二级类型名称
         from (
                  select waybill_no
                       , problem_type_code         --问题件类型编码
                       , problem_type_name         --问题件类型名称
                       , problem_type_subject_code --问题件二级类型编码
                       , problem_type_subject_name --问题件二级类型名称
                       , row_number() over (partition by waybill_no order by update_time desc ) as rn
                  from jms_dwd.dwd_sqs_problem_express_wo_base_dt --问题件工单清洗表
                  where dt >= date_add('{{ execution_date |  cst_ds }}', -45)
                    and dt <= '{{ execution_date |  cst_ds }}'
                    and work_order_status = 6
                    and problem_type_code = 27
                    and waybill_no is not null
              ) t1
         where rn = 1
     ),
     arbitration_type as (--丢失量
         select waybill_no
         from jms_dwd.dwd_arbitration_dt
         where dt >= date_add('{{ execution_date |  cst_ds }}', -45)
           and dt <= '{{ execution_date |  cst_ds }}'
           and status = 10          -- 10、已结案
           and first_type_code = 03 --遗失大类 03
           and waybill_no is not null
         group by waybill_no
     ),
     one_cus_ord_arb as (
         select t2.project_name                                                    --项目名称
              , t2.code                                                            --客户编码
              , t2.name                                                            --客户名称
              , t2.customer_network_code                                           --客户所属网点code
              , t2.customer_network_name                                           --客户所属网点name
              , t2.customer_center_code                                            --客户所属中心code
              , t2.customer_center_name                                            --客户所属中心name
              , t2.customer_agent_code                                             --客户所属代理区name
              , t2.customer_agent_name                                             --客户所属代理区name
              , t1.order_id                                                        --订单编号
              , t1.waybill_id                                                      --运单号
              , t1.pick_time                                                       --取件时间(揽收时间)
              , t1.input_time                                                      --订单录入时间
              , t1.add6h_input_time                                                --录入时间往后+6小时
              , t1.add6h_date_time                                                 --日期(录入时间往后+6小时)
              , case
                    when t1.pick_time <= concat(substr(t1.add6h_date_time, 1, 10), ' 23:59:59') then 1
                    else 0 end                           as add6h_timely_pick_falg --1代表及时揽收标识
              , t1.real_pick_network_code                                          --实际取件网点code(揽收网点)
              , t1.real_pick_network_name                                          --实际取件网点名称(揽收网点)
              , t1.dispatch_network_code                                           --派件网点code
              , t1.dispatch_network_name                                           --派件网点名称
              , t1.cancel_flag                                                     --订单取消1代表取消,0代表未取消
              , t1.order_status_code                                               --订单状态code
              , t1.real_pick_provider_id                                           --(揽收网点)实际取件网点省id
              , t1.real_pick_provider_desc                                         --(揽收网点)实际取件网点省desc
              , t1.real_pick_city_id                                               --(揽收网点)实际取件网点市id
              , t1.real_pick_city_desc                                             --(揽收网点)实际取件网点市desc
              , t1.dispatch_provider_id                                            --派件网点省id
              , t1.dispatch_provider_desc                                          --派件网点省desc
              , t1.dispatch_city_id                                                --派件网点市id
              , t1.dispatch_city_desc                                              --派件网点市desc
              , if(t3.waybill_no is not null, 1, 0)      as arbitration_flag       --1代表丢失
              , t4.platform_effect                       as platform_effect_days   --平台时效天数
              , if(t4.platform_effect is not null, 1, 0) as platform_effect_falg   --1代表有平台时效
         from customer_project t2
                  join order_incre t1
                       on t1.customer_code = t2.code
                  left join arbitration_type t3
                            on t1.waybill_id = t3.waybill_no
                  left join platform_effect t4
                            on t1.real_pick_provider_desc = t4.start_province
                                and t1.real_pick_city_desc = t4.start_city
                                and t1.dispatch_provider_desc = t4.end_province
                                and t1.dispatch_city_desc = t4.end_city
                                and (t1.pick_time > effect_start_time and t1.pick_time < effect_end_time)
     )
insert
overwrite
table
jms_dm.dm_mng_cn_project_order_detail_before_dt
partition
(
dt
)
select t1.project_name                                                                                  --项目名称
     , t1.code                                                                                          --客户编码
     , t1.name                                                                                          --客户名称
     , t1.customer_network_code                                                                         --客户所属网点code
     , t1.customer_network_name                                                                         --客户所属网点name
     , t1.customer_center_code                                                                          --客户所属中心code
     , t1.customer_center_name                                                                          --客户所属中心name
     , t1.customer_agent_code                                                                           --客户所属代理区name
     , t1.customer_agent_name                                                                           --客户所属代理区name
     , t1.order_id                                                                                      --订单编号
     , t1.waybill_id                                                             as waybill_no          --运单号
     , t1.pick_time                                                                                     --取件时间(揽收时间)
     , t1.input_time                                                                                    --订单录入时间
     , t1.add6h_input_time                                                                              --录入时间往后+6小时
     , t1.add6h_date_time                                                        as date_time           --日期(录入时间往后+6小时)
     , t1.add6h_timely_pick_falg                                                                        --1代表及时揽收标识
     , t1.real_pick_network_code                                                                        --实际取件网点code(揽收网点)
     , t1.real_pick_network_name                                                                        --实际取件网点名称(揽收网点)
     , t1.dispatch_network_code                                                                         --派件网点code
     , t1.dispatch_network_name                                                                         --派件网点名称
     , t1.cancel_flag                                                                                   --订单取消1代表取消,0代表未取消
     , t1.order_status_code                                                                             --订单状态code
     , t1.real_pick_provider_desc                                                                       --(揽收网点)实际取件网点省desc
     , t1.real_pick_city_desc                                                                           --(揽收网点)实际取件网点市desc
     , t1.dispatch_provider_desc                                                                        --派件网点省desc
     , t1.dispatch_city_desc                                                                            --派件网点市desc
     , t1.arbitration_flag                                                                              --1代表丢失
     , t1.platform_effect_days                                                                          --平台时效天数
     , t1.platform_effect_falg                                                                          --1代表有平台时效
     , least(t2.sign_time, t5.input_time)                                        as sign_time           --签收时间
     , t2.sign_network_code                                                                             --签收网点code
     , t2.sign_network_name                                                                             --签收网点名称
     , t2.way_input_time                                                                                --运单录入时间
     , t2.waybill_status_code                                                                           --运单状态code
     , case when t2.sign_falg = 1 or t5.input_time is not null then 1 else 0 end as sign_falg           --签收标识1正常签收和退件签收 0未签收
     , if(t2.waybill_no is not null, 1, 0)                                       as is_waybill_flag     --1实际揽收指标(产生运单)
     , t3.problem_type_code                                                                             --破损量的问题件类型编码
     , t3.problem_type_name                                                                             --破损量的问题件类型名称
     , t3.problem_type_subject_code                                                                     --破损量的问题件二级类型编码
     , t3.problem_type_subject_name                                                                     --破损量的问题件二级类型名称
     , if(t3.waybill_no is not null, 1, 0)                                       as express_flag        --1代表破损标识
     , from_unixtime(unix_timestamp(t1.pick_time) + t1.platform_effect_days * 518400 + 21600,
                     'yyyy-MM-dd HH:mm:ss')                                      as m6_addeff_pick_time --揽收时间加上时效天数的时间+6天 +6小时
     , from_unixtime(unix_timestamp(t1.pick_time) + t1.platform_effect_days * 604800 + 21600,
                     'yyyy-MM-dd HH:mm:ss')                                      as m7_addeff_pick_time --揽收时间加上时效天数的时间+7天+6小时
     , substr(from_unixtime(unix_timestamp(t1.pick_time) + t1.platform_effect_days * 86400 + 21600,
                            'yyyy-MM-dd HH:mm:ss'), 1,
              10)                                                                as pick_date_time      --日期(揽收时间加上时效天数的时间) +6小时
     , t4.agent_code                                                                                    --实际取件网点code(揽收网点)代理区code
     , t4.agent_name                                                                                    --实际取件网点code(揽收网点)代理区name
     , t4.in_distribution_code_new                                               as center_code         --实际取件网点code(揽收网点)中心code
     , t4.in_distribution_name_new                                               as center_name         --实际取件网点code(揽收网点)中心name
     , t1.add6h_date_time                                                        as dt                  --加6小时的日期
from one_cus_ord_arb t1
         left join waybill_incre t2
                   on t1.waybill_id = t2.waybill_no
         left join express_wo t3
                   on t1.waybill_id = t3.waybill_no
         left join jms_dim.dim_network_whole_massage t4
                   on t1.real_pick_network_code = t4.code
         left join collect_base t5
                   on t2.waybill_no = t5.waybill_no
where t1.add6h_date_time <= '{{ execution_date |  cst_ds }}'
  and t1.add6h_date_time >= date_add('{{ execution_date |  cst_ds }}', -45)
    distribute by dt
;
